package cn.laiyoufa.imple;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import cn.common.dao.BaseDao;
import cn.laiyoufa.model.VipModel;

public class VipDao extends BaseDao{
   public String insert(VipModel vip){
   Connection conn=this.getConn();
   String sql="insert into vip (v_name,v_type,v_sex,v_number) values(?,?,?,?) ";
   PreparedStatement pstmt;
   try 
   {
	 pstmt = conn.prepareStatement(sql);
     pstmt.setString(1, vip.getV_name());
     pstmt.setString(2, vip.getV_type());
     pstmt.setString(3, vip.getV_sex());
     pstmt.setString(4, vip.getV_number());
     int i=pstmt.executeUpdate();
     if(i==1)
     {
	   System.out.println("insert success");
	   pstmt.close();
	   conn.close();
	   } 
	}
   catch (SQLException e) 
   {
		e.printStackTrace();
   } 
    return null ;
   }
   
   public String update(VipModel vip) {
	   Connection conn=this.getConn();
	   String sql="update vip set v_name=?,v_type=?,v_sex=?,v_number=? where v_id=?";
	   PreparedStatement pstmt;
	try {
		pstmt = conn.prepareStatement(sql);
		 pstmt.setString(1, vip.getV_name());
	     pstmt.setString(2, vip.getV_type());
	     pstmt.setString(3, vip.getV_sex());
	     pstmt.setString(4, vip.getV_number());
	     pstmt.setString(5,vip.getV_id());
	    int i=pstmt.executeUpdate();
	    if(i==1){
			 System.out.println("update success");
			 pstmt.close();
			 conn.close();
		   }	
	} catch (SQLException e) {		
		e.printStackTrace();
	}
	  		  		   
	   return null;
	   
   }
   
   public ArrayList<VipModel> select(){  //首次进入页面的查找询
	   ArrayList list=new ArrayList();
	   Connection conn=this.getConn();
	   String sql="select * from vip ";
	   try 
	   {
		   PreparedStatement pstmt=conn.prepareStatement(sql);
		  // pstmt.setString(1, "1");
		   ResultSet rs=pstmt.executeQuery();
		while(rs!=null&&rs.next())
		{
			VipModel vip=new VipModel();
			vip.setV_id(rs.getString("v_id"));
			vip.setV_name(rs.getString("v_name"));
			vip.setV_type(rs.getString("v_type"));
			vip.setV_sex(rs.getString("v_sex"));
			vip.setV_number(rs.getString("v_number"));
			list.add(vip);
		}
	} 
	catch (SQLException e) 
	{		
		e.printStackTrace();
	}   
	return list;   
   }
   
   /*
    * 模糊查询
    */
   public ArrayList<VipModel> select_like(String type,String key)
   {
	   Connection conn=this.getConn();
	   ArrayList<VipModel> list=new ArrayList<VipModel>();
	   String sql="select * from vip where "+type+" like ?";
	   System.out.println(type+" "+key);
	   try 
	   {
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, "%"+key+"%");
			ResultSet rs=pstmt.executeQuery();
			while(rs!=null&&rs.next())
			{
				VipModel vip=new VipModel();
				vip.setV_id(rs.getString("v_id"));
				vip.setV_name(rs.getString("v_name"));
				vip.setV_type(rs.getString("v_type"));
				vip.setV_sex(rs.getString("v_sex"));
				vip.setV_number(rs.getString("v_number"));
				list.add(vip);
			}
		
	} 
	catch (SQLException e) 
	{
		e.printStackTrace();
	}
	return list;   
   }
   
   public String delete(String v_id){
	   Connection conn=this.getConn();
	   String sql="update vip set v_type=? where v_id=?";
	   
	   try 
	   {
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, "0");
			pstmt.setInt(2, Integer.parseInt(v_id));
			int i=pstmt.executeUpdate();
			if(i==1)
			{
				System.out.println("delete success");
			}
		} 
	    catch (SQLException e) 
		{		
			e.printStackTrace();
		}
	   
	return null;
	   
   }
}
